﻿using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using UsedBook.Models;
using UsedBook.Utils;

namespace UsedBook.DAL
{
    public class UserDAL
    {
        // 查询所有用户
        #region 查询所有用户  public List<User> GetAllUsers()
        public List<User> GetAllUsers()
        {
            List<User> users = new List<User>();
            using (SqlConnection conn = DBHelper.GetSqlConnection())
            {
                conn.Open();
                string sql = "select * from Users";
                using (SqlCommand cmd = new(sql, conn))
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            User user = new User();
                            user.UserId = reader.GetInt32(0);
                            user.UserName = reader.GetString(1);
                            user.Pwd = reader.GetString(2);
                            user.Email = reader.GetString(3);
                            user.Phone = reader.GetString(4);
                            user.UserRole = reader.GetString(5);
                            user.CreateAt = reader.GetDateTime(6);
                            users.Add(user);
                        }
                        return users;
                    }
                }
            }
        }
        #endregion

        // 根据用户ID查询用户
        #region 根据用户ID查询用户 public User GetUserById(int id)
        public User GetUserById(int id)
        {
            using (SqlConnection conn = DBHelper.GetSqlConnection())
            {
                conn.Open();
                string sql = "select * from Users where UserId = @UserId";
                SqlParameter sqlParameter = new SqlParameter("UserId", id);
                using (SqlCommand cmd = new(sql,conn))
                {
                    cmd.Parameters.Add(sqlParameter);
                    using(SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            User user = new User()
                            {
                                UserId = reader.GetInt32(0),
                                UserName = reader.GetString(1),
                                Pwd = reader.GetString(2),
                                Email = reader.GetString(3),
                                Phone = reader.GetString(4),
                                UserRole = reader.GetString(5),
                                CreateAt = reader.GetDateTime(6)
                            };
                            return user;
                        }
                        else
                        {
                            return new User();
                        }
                    }
                }
            }
        }

        #endregion

        // 根据用户Id删除用户
        #region 根据用户Id删除用户 public int DeleteUserById(int id)
        public bool DeleteUserById(int id)
        {
            using (SqlConnection conn = DBHelper.GetSqlConnection())
            {
                conn.Open();
                string sql = "delete from Users where UserId = @UserId";
                SqlParameter sqlParameter = new SqlParameter("UserId", id);
                using (SqlCommand cmd = new(sql,conn))
                {
                    cmd.Parameters.Add(sqlParameter);
                    int row = cmd.ExecuteNonQuery();
                    return row>0;
                }
            }
        }
        #endregion

        // 新增加用户
        #region 新增加用户 public bool AddUser(User newUser)
        public bool AddUser(User newUser)
        {
            using (SqlConnection conn = DBHelper.GetSqlConnection())
            {
                conn.Open();
                string sql = "insert into Users values(@UserName,@Pwd,@Email,@Phone)";
                SqlParameter[] sqlParameters =
                {
                    new SqlParameter("UserName", newUser.UserName),
                    new SqlParameter("Pwd",newUser.Pwd),
                    new SqlParameter("Email", newUser.Email),
                    new SqlParameter("Phone",newUser.Phone)
                };
                using (SqlCommand sqlCommand = new(sql, conn))
                {
                    sqlCommand.Parameters.Add(sqlParameters);
                    int row = sqlCommand.ExecuteNonQuery();
                    return row>0;
                }
            }
        }
        #endregion

        // 修改用户 public bool UpdateUser(User updateUser)
        #region 修改用户 public bool UpdateUser(User updateUser)
        public bool UpdateUser(User updateUser)
        {
            using (SqlConnection conn = DBHelper.GetSqlConnection())
            {
                conn.Open ();
                string sql = "update Users set UserName=@UserName,Pwd=@Pwd,Email=@Email,Phone=@Phone,UserRole=@UserRole,CreateAt=@CreateAt";
                SqlParameter[] sqlParameters =
                {
                    new SqlParameter("UserName",updateUser.UserName),
                    new SqlParameter("Pwd",updateUser.Pwd),
                    new SqlParameter("Email",updateUser.Email),
                    new SqlParameter("Phone",updateUser.Phone),
                    new SqlParameter("UserRole",updateUser.UserName),
                    new SqlParameter("CreateAt",updateUser.CreateAt)
                };
                using (SqlCommand cmd = new(sql,conn))
                {
                    cmd.Parameters.AddRange(sqlParameters);
                    int rows = cmd.ExecuteNonQuery();
                    return rows>0;
                }
            }
        }
        #endregion


    }
}
